# -*- coding: utf-8 -*-
"""
Created on Fri May  5 13:27:30 2023

@author: LJM
"""

import seaborn as sns 
import matplotlib.pyplot as plt
import pandas as pd
import re
import json
import numpy as np
import time

def get_time(s_time):
    e_time = time.time()
    
    run_time = e_time - s_time
    print(run_time / 60 ,'分钟')


def expression_fun(cut_value, value):
    """

    :param cut_value: 含有公式的分箱
    :param value: 分箱所对应的值
    :return: 带入公式返回相应值
    """
    x = value
    return eval(cut_value)


def cond_parse(data, condition):
    """
    :param data: 数据源
    :param condition: 转换条件
    :return: 返回满足条件的位置布尔值
    """
    condition_cp = condition
    condition = condition.lower()
    if condition == 'none':
        cond = pd.isnull(data)
    elif condition == 'else':
        assert data.dtype == bool, '为else时，data需给出之前结果的并集'
        cond = ~data  # 反转所有已经处理过的条件的布尔值，得到其他情况
    else:
        p = re.match(r'\((\S+),\s*(\S+)[\]\)]', condition)
        if p:  # 数值型特征
            p = p.groups()
            cond = pd.notnull(data)
            if p[0].lower() != '-inf':
                cond = cond & (data > float(p[0]))
            if p[1].lower() != '+inf':
                cond = cond & (data <= float(p[1]))
        else:  # 类别型特征
            cond = json.loads(condition_cp)
            cond = data.apply(lambda x: x in cond)
            # cond=data.apply(lambda x: bool(re.search(x,str(cond))) if isinstance(x,str) else False)
    return cond


# def transform_value_to_score(binning, data, score='score', remove=[]):
#     """

#     :param binning: 分箱表
#     :param data: 数据源
#     :param score: 分箱分数
#     :param remove:
#     :return: 转换分数
#     """
#     res_score = data.copy()
#     for var_name in binning['var_name'].unique():
#         if var_name not in data.columns and var_name not in remove:
#             continue
#         cond_ = cond_parse(data[var_name], 'None')
#         index = binning[(binning['var_name'] == var_name) & (binning['split_list'] == 'None')].index[0]
#         res_score.loc[cond_, var_name] = binning.loc[index, score]
#         for index in binning[binning['var_name'] == var_name].index:
#             condition = binning.loc[index, 'split_list']
#             if condition.lower() in ['none', 'else']:
#                 continue
#             _cond = cond_parse(data[var_name], condition)
#             cond_ = cond_ | _cond
#             res_score.loc[_cond, var_name] = binning.loc[index, score]
#             # 处理分箱中带公式的分数
#             for i in range(res_score.shape[0]):
#                 if "x" in str(res_score[var_name][i]):
#                     res_score[var_name][i] = expression_fun(res_score[var_name][i], data[var_name][i])
#                 else:
#                     continue
#         _cond = cond_parse(cond_, 'Else')
#         index = binning[(binning['var_name'] == var_name) & (binning['split_list'] == 'Else')].index[0]
#         res_score.loc[_cond, var_name] = binning.loc[index, score]
#     return res_score


def transform_value_to_score(binning, data, score ='score', remove=[]):
    res_score = data.copy()
    for var_name in binning['var_name'].unique():
#         print(var_name)
        if var_name not in data.columns and var_name not in remove:
            continue
        cond_= cond_parse(data[var_name],'None')
        index = binning[(binning ['var_name']==var_name)&(binning['split_list']=='None')].index[0] 
        res_score.loc[cond_, var_name] = binning.loc[index, score]
        for index in binning.loc[binning['var_name']==var_name].index:
            condition = binning.loc[index,'split_list']
            if condition.lower() in ['none', 'else']:
                continue
            _cond = cond_parse(data[var_name], condition)
            cond_ = cond_|_cond 
            res_score.loc[_cond,var_name]= binning.loc[index, score]
        for idx in res_score.index:
            if "x" in str(res_score.loc[idx,var_name]):
                res_score.loc[idx,var_name] = expression_fun(res_score.loc[idx,var_name],data.loc[idx,var_name])
            else:
                continue
        _cond = cond_parse(cond_,'Else')
        index = binning[(binning['var_name']==var_name)&(binning['split_list']=='Else')].index[0]
        res_score.loc[_cond, var_name]= binning.loc [index, score]
    return res_score


def to_float(x):
    try:
        x = float(x)
    except:
        x = np.nan
        print('该取值转换为数值失败：'+x)
    return x

def get_ruled(ur_df):
    df = ur_df.copy()
    df['if_e0'] = ''
    df.loc[(df['ep_rating_12m_cate'] == '4') | (df['pnsh_ep_12m_cate'] == '5'), 'if_e0'] = 'E'
    df['if_s0'] = ""
    df.loc[df['pnsh_mem_12m_cate'] == '5', 'if_s0'] = 'S'
    df['if_g0'] = ''
    df.loc[df['audit_comt_12m_cate'].isin(['2', '3', '4']), 'if_gO'] = 'G'
    l = [['compul_execu_12m_if', '1'], ['pnsh_break_12m_if', '1'], ['pnsh_case_12m_cate', '5'],
         ['abnoma_opera_12m_cate', 4],
         ['criminal_case_12m_if', '1'], ['liquidation_12m_if', '1'], ['tax_abnoma_if', '1'],
         ['illegal_breach_12m_if', '1']]
    for i in l:
        df.loc[df[i[0]] == i[1], 'if_gO'] = 'G'
    df["ESG_if_0"] = df['if_eO'] + df['if_s0'] + df['if_g0']
    del df['if_eO'], df['if_s0'], df['if_g0']
    return df[['needdate', 'customer_no', 'ESG_if_0']].copy()


def rule_score_dafenka(data_x_f, data_data_use, df_score, nobias):
    """
    :param data_x_f: 转换为分数之后的矩阵
    :param data_data_use: x的原始数据
    :param df_score: 分箱赋分表
    :param nobias:  e，s，g分别有哪些特征
    :return:
    """
    feat_e_used_nobias, feat_s_used_nobias, feat_g_used_nobias = nobias[0], nobias[1], nobias[2]
    zc_score = df_score.groupby('var_name')['score'].min().to_dict()  # 两个区别只在这
    date_rule = get_ruled(data_data_use)
    date_rule = pd.concat([data_x_f.reset_index(drop=True),
                           date_rule.reset_index(drop=True)], axis=1)
    del date_rule['customer_no']
    del date_rule['needdate']
    for i in feat_e_used_nobias:
        date_rule.loc[date_rule['ESG_if_0'].str.contains('E'), i] = zc_score[i]
    for i in feat_s_used_nobias:
        date_rule.loc[date_rule['ESG_if_0'].str.contains('S'), i] = zc_score[i]
    for i in feat_g_used_nobias:
        date_rule.loc[date_rule['ESG_if_0'], str.contains('G'), i] = zc_score[i]
    del date_rule['ESG_if_0']
    return date_rule


def to_float(x):
    try:
        x = float(x)
    except:
        x = np.nan
        print('该取值转换为数值失败：'+x)
    return x

#%%
#model_type = 'fzy_s'
#batch_id = 'ESG_DATA_202304'
#dfsc = pd.read_excel('dfsc2.xlsx')
#dfwt = pd.read_excel('dfwt2.xlsx')

#df_dim = pd.read_excel('df_dim2.xlsx')


# 数值型指标，ESG类型
def get_dimlist(df_dim): # 传入df  返回 获取维度划分列表 以及特征类型转换列表
    floint = list(df_dim.loc[df_dim['type']=='数值','var_name'])
    elist = list(df_dim.loc[df_dim['lvl_1']=='E','var_name'])
    slist = list(df_dim.loc[df_dim['lvl_1']=='S','var_name'])
    glist = list(df_dim.loc[df_dim['lvl_1']=='G','var_name'])
    strf = list(df_dim.loc[df_dim['type']=='字符','var_name'])
    return elist,slist,glist,floint,strf
#elist,slist,glist,floint = get_dimlist(df_dim)
#%%
def get_score_weight_bulk(model_type,dfsc,dfwt):  #对赋分表与权重表进行切片 
    print('')
    print('开始该模型下的数据切片')
    dfs = dfsc.loc[dfsc['mdlcode']==model_type].iloc[:,1:].copy()
    dfw = dfwt.loc[dfwt['mdlcode']==model_type].iloc[:,1:].copy()
    dfs.reset_index(drop=True,inplace=True) #小分数
    dfw.reset_index(drop=True,inplace=True)  #权重
    print('赋分表与权重表的切片已完成')
    print('')
    return dfs,dfw 
#dfs,dfw = get_score_weight_bulk(model_type)

#%%
def data_cut(dfjch,dfo,model_type,dfw,if_all='inner'): #对总的特征数据进行 横向与纵向的切割 （筛选）
    print('')
    print('开始该模型下的数据切片')
    #横向切割
    dfjch_cut = dfjch.loc[dfjch.model_type == model_type].copy()    #按模型分客户
    dfo_cut = dfo.loc[dfo.custname.isin(dfjch_cut.custname)].copy() #留下该模型客户群体的特征数据（横向切割） 可以加一个左连接来保证主表数量
    #dfjch_cut =dfjch_cut.loc[dfjch_cut.custname.isin(dfo_cut.custname)].copy()   
    
    df = find_null_feature_cust(dfjch_cut,dfo_cut,model_type)                    #记录缺少蚂蚁数据的企业
    #纵向切割
    dfo_cut = dfo_cut.set_index(['rating_id']).stack().reset_index()    #转换成窄表
    dfo_cut.columns = ['rating_id','index_name','index_content']
    dfo_cut = dfo_cut.loc[dfo_cut.index_name.isin(dfw.var_name)].copy() #限定数据集的特征个数（纵向切割 由模型分类定多少个特征）
    #保持两个表数据一致 然后删除名字
    dfo_cut = dfo_cut.set_index(['rating_id','index_name']).index_content.unstack()  #仍然返回宽表
    dfo_cut = pd.merge(dfjch_cut[['rating_id']],dfo_cut,on='rating_id',how=if_all) #保持两个切片样本量一致
    dfo_cut.set_index('rating_id',inplace=True)
    print('特征表与基础信息表的切片已完成')
    
    
    
    print('')
    return dfjch_cut,dfo_cut,df
#dfjch_cut,dfo_cut = data_cut(dfjch,dfo,model_type,dfw)
#%%

#保证特征表的特征个数与该模型所定义的特征个数保持一致
def keep_same_feature(dfo_cut,dfw):
    print('开始使特征数量一致')
    for col in [x for x in dfw['var_name'] if x not in dfo_cut.columns]:
        print(col,'这个特征纳入模型但是特征表中没有 现在用空值填充')
        dfo_cut[col] = np.nan
    print('特征已经一致')
#keep_same_feature(dfo_cut,dfw)

#对于特征表中的数值型字符串数据都转化为浮点型
# def convert_feature_type(dfo_cut,floint):
#     print('开始特征类型转换')
#     for col in floint:
#         if col in dfo_cut.columns:
#             dfo_cut[col] = dfo_cut[col].apply(to_float)
#     print('类型转换结束')

def convert_feature_type(dfo_cut,floint,strf):
    print('开始特征类型转换')
    for col in floint:
        if col in dfo_cut.columns:
            dfo_cut[col] = dfo_cut[col].apply(to_float)
    for col in strf:
        if col in dfo_cut.columns:
            # print(col)
            dfo_cut[col] = dfo_cut[col].apply(to_string)
    print('类型转换结束')


def to_string(x):
    if str(x)  ==  'nan' or x is np.nan:
        x = x
    else:
        try:
            x = str(int(x))  #参数表都是1，2，3，4才可以用这个 否则不可以！！！！
        except:
            x = np.nan
            print('转换有问题')
    return x


#convert_feature_type(dfo_cut,floint)
#%%
#dfo_cut = pd.merge(dfjch_cut[['rating_id','industry_code']],dfo_cut,how='left',on='rating_id')

#%%
def get_factor_score(dfs,dfo_cut):  #传入赋分表 特征切片表 返回不带权重的因子得分
    print('')
    print('开始转换分数')
    # print(dfs)
    df = transform_value_to_score(dfs,dfo_cut)
    
    # df.iloc[:,1:] = df.iloc[:,1:].astype(float)
    df = df.astype('float')
    for col in df.columns:                   #有均值填充的需求先赋值成-1然后 改成na 最后fillna
        # print(df[col])
        df.loc[df[col]<0,col] = np.nan
        df[col].fillna(df[col].mean(),inplace=True)
    print('分数已经转换成功')
    print('')
    return df

def fill_mean_value(df,mean_lst):   #分数转换那一步中如果有需要填充均值的 先让返回的分数为-1  然后利用这个函数将小于0的赋值为空值 然后利用均值填充
    print('开始填充均值')            #mean_lst 是记录被均值填充的特征的列表
    for col in mean_lst:
        df.loc[df[col] < 0,col] = np.nan
        df[col].fillna(df[col].mean(),inplace=True)
        df[col].fillna(0,inplace=True)
        return df
    print('均值填充结束')
#df = get_factor_score(dfs,dfo_cut)
#df = df.reset_index()
#del df['industry_code']
#%%
def get_weight_score(df,dfw): #传入无权重分数 与权重表  返回乘完权重的分数窄表
    print('')
    print('开始与权重相乘')  
    temp = df.set_index('rating_id').stack().reset_index()
    temp.columns = ['rating_id','feature','score']
    dfw['weight'] = dfw['weight'] / dfw['weight'].sum() * 100
    data = pd.merge(temp,dfw[['var_name','weight']],left_on = 'feature',right_on='var_name')
    del data['feature']
    data['weight_score'] = data.weight * data.score
    print('已返回乘权重后的中间数据窄表')
    print('')
    return data


#data = get_weight_score(df,dfw)
#如果有需要可向数据库中写入带权重的特征分数
#feature_score_weight = data[['rating_id','var_name','weight_score']]


def get_model_rating_result(data,elist,slist,glist,model_type):    #传入分数窄表 返回聚合后的e,s,g,esg分数
    print('')
    print('开始计算结果表')   
    df_result = data.set_index(['rating_id','var_name']).weight_score.unstack()
    df_result['esg_score'] = df_result.sum(axis=1)
    df_result['e_score'] = df_result[[col  for col in df_result.columns if col in elist ]].sum(axis=1)
    df_result['s_score'] = df_result[[col  for col in df_result.columns if col in slist]].sum(axis=1)
    df_result['g_score'] = df_result[[col  for col in df_result.columns if col in glist]].sum(axis=1)
    # print('{}客户时间添加完毕'.format(model_type))
    
    df_result['esg_time'] = time.strftime('%Y%m%d %H:%M:%S',time.localtime())
    df_result.reset_index(inplace=True)
    result = df_result[['rating_id','e_score','s_score','g_score','esg_score','esg_time']]
    result['model_type'] = model_type
    # print(result.esg_time)
    print('结果表计算结束')
    print('')
    return result
#result = get_model_rating_result(data,elist,slist,glist)
#%%
#找到蚂蚁数据缺失的企业 形成一个df
def find_null_feature_cust(dfjch_cut,dfo_cut,model_type):
    lst = [i for i in dfjch_cut.custname if i not in list(dfo_cut.custname)]
    df = pd.DataFrame(lst).rename(columns={0:'custname'})
    df['model_type'] = model_type
    return df


#需要变量化修改的地方  dfjch基础信息表 dfo特征表  赋分表dfsc  权重表 dfwt   维度表 df_dim  (生成e,s,g三维度列表 与数据类型转换列表)


#%%


def plotHist(result):
    print(result.esg_score.describe())
    fig = plt.figure(figsize=(10,6))

    sns.distplot(result.esg_score,hist=True,bins=80)

    plt.xlim(0,90)
    plt.show()


#%%

